﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Data.Entity;
using Kendo.Mvc.UI;
using Kendo.Mvc.Extensions;
using TS2015.Models;
using System.Web.Script.Serialization;
using System.Data.OleDb;
using System.Data;
using System.Data.Entity.Validation;
using System.Text;
//using System.Web.UI.WebControls;
//using System.IO;
//using System.Web.UI;


namespace TS2015.Areas.Admin.Controllers
{
    public class RegistrationManagementController : Controller
    {
        TS2015Context db = new TS2015Context();
        // GET: Admin/RegistrationManagement
        bool CheckDepartment, CheckPhylum, CheckArea, CheckGroup, CheckEthnic, CheckProvince, CheckCommune,
            CheckPriorityGroup, CheckGraduateSubject, CheckGraduateSubject_Group;
        string IDDepartment, IDPhylum, IDGraduateSubject;
        List<string> error = new List<string>();
        string count;

        #region TrainingSystem
        public JsonResult GetTrainingSystem()
        {
            return Json(db.TbHeDaoTaos.ToList(), JsonRequestBehavior.AllowGet);
        }
        public ActionResult TrainingSystem()
        {
            if (!Request.IsAjaxRequest())
                return View();

            return PartialView();
        }
        public ActionResult EditingPopup_ReadTrainingSystem([DataSourceRequest] DataSourceRequest request)
        {
            var x = Request.QueryString["idpage"];
            return Json(db.TbHeDaoTaos.ToDataSourceResult(request));
        }

        //[ValidateAntiForgeryToken]
        public ActionResult EditingPopup_CreateTrainingSystem([DataSourceRequest] DataSourceRequest request, TbHeDaoTao data)
        {
            if (data != null && ModelState.IsValid)
            {
                db.TbHeDaoTaos.Add(data);
                db.SaveChanges();
            }
            return Json(ModelState.ToDataSourceResult());
        }
        public ActionResult EditingPopup_UpdateTrainingSystem([DataSourceRequest] DataSourceRequest request, TbHeDaoTao data)
        {
            if (data != null && ModelState.IsValid)
            {
                db.Entry(data).State = EntityState.Modified;
                db.SaveChanges();
            }
            return Json(ModelState.ToDataSourceResult());
        }
        public ActionResult EditingPopup_DestroyTrainingSystem([DataSourceRequest] DataSourceRequest request, TbHeDaoTao data)
        {
            if (data != null)
            {
                TbHeDaoTao itemdata = db.TbHeDaoTaos.First(item => item.MaHeDaoTao == data.MaHeDaoTao);
                db.TbHeDaoTaos.Remove(itemdata);
                db.SaveChanges();
            }
            return Json(ModelState.ToDataSourceResult());
        }
        #endregion
        #region Department
        //[OutputCache(Duration=600)]
        public JsonResult GetDepartment()
        {
            return Json(db.TbKhoas.ToList(), JsonRequestBehavior.AllowGet);
        }
        public ActionResult Department()
        {
            if (!Request.IsAjaxRequest())
                return View();

            return PartialView();
        }
        public ActionResult EditingPopup_ReadDepartment([DataSourceRequest] DataSourceRequest request)
        {
            return Json(db.TbKhoas.ToDataSourceResult(request));
        }

        public ActionResult EditingPopup_CreateDepartment([DataSourceRequest] DataSourceRequest request, TbKhoa data)
        {
            if (data != null && ModelState.IsValid)
            {
                db.TbKhoas.Add(data);
                db.SaveChanges();
            }
            return Json(ModelState.ToDataSourceResult());
        }

        public ActionResult EditingPopup_UpdateDepartment([DataSourceRequest] DataSourceRequest request, TbKhoa data)
        {
            if (data != null && ModelState.IsValid)
            {
                db.Entry(data).State = EntityState.Modified;
                db.SaveChanges();
            }
            return Json(ModelState.ToDataSourceResult());
        }
        public ActionResult EditingPopup_DestroyDepartment([DataSourceRequest] DataSourceRequest request, TbKhoa data)
        {
            if (data != null)
            {
                TbKhoa itemdata = db.TbKhoas.First(item => item.MaKhoa == data.MaKhoa);
                db.TbKhoas.Remove(itemdata);
                db.SaveChanges();
            }
            return Json(ModelState.ToDataSourceResult());
        }
        [HttpPost]
        [ValidateAntiForgeryToken]
        public ActionResult ImportDepartment(HttpPostedFileBase FileImport)
        {
            bool state = false;
            string mess = "";
            if (FileImport != null)
            {
                // thuc hien thao tac tai day
                if (FileImport.ContentLength > 0)
                {
                    string Extension = System.IO.Path.GetExtension(FileImport.FileName);
                    if (Extension == ".xls" || Extension == ".xlsx")
                    {
                        string path = string.Concat(Server.MapPath("~/Uploads/File/" + DateTime.Now.ToString("MMddyyyyHHmmssfff") + FileImport.FileName));
                        //if (System.IO.File.Exists(path))
                        //{

                        //    System.IO.File.Delete(path);
                        //}
                        FileImport.SaveAs(path);
                        OleDbConnection oledbcon = new OleDbConnection("provider=microsoft.ace.oledb.12.0;data source=" + path + ";extended properties=excel 12.0;");
                        oledbcon.Open();
                        DataTable ExcelSheets = oledbcon.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                        string SpreadSheetName = ExcelSheets.Rows[0]["TABLE_NAME"].ToString();
                        OleDbCommand oledbcmd = new OleDbCommand("Select * from [" + SpreadSheetName + "]", oledbcon);
                        OleDbDataAdapter oledbda = new OleDbDataAdapter(oledbcmd);

                        DataTable dt = new DataTable();
                        var khoa = db.TbKhoas.Select(m => m.TenKhoa);
                        TbKhoa item;
                        oledbda.Fill(dt);
                        if (dt.Rows.Count > 0)
                        {
                            if (!dt.Columns.Contains("Khoa"))
                            {
                                error.Add("Cột Khoa không tồn tại!");
                                goto next;
                            }
                            for (int i = 0; i < dt.Rows.Count - 1; i++)
                            {
                                foreach (var a in khoa)
                                {
                                    CheckDepartment = false;
                                    if (a.ToString().ToLower().Trim() == dt.Rows[i]["Khoa"].ToString().ToLower().Trim())
                                    {
                                        CheckDepartment = true;
                                        break;
                                    }
                                }
                                //try
                                //{
                                if(!CheckDepartment)
                                {
                                    item = new TbKhoa();
                                    item.TenKhoa = dt.Rows[i]["Khoa"].ToString().Trim();
                                    db.TbKhoas.Add(item);
                                    db.SaveChanges();
                                }
                                else
                                {
                                    error.Add("Khoa: " + dt.Rows[i]["Khoa"].ToString() + " đã tồn tại!");
                                }
                                //}
                                //catch
                                //{
                                //}
                            }
                            for (int k = 0; k < error.Count; k++)
                            {
                                count += " + " + error[k].ToString() + "<br/>";
                            }
                        next:
                            state = true;
                            mess = "Thực hiện chèn thành công " + (dt.Rows.Count - error.Count) + " bản ghi!";
                            //TempData["Message"] = "Lưu thành công!";
                        }
                        else
                        {
                            Response.Write("Bạn phải nhập file định dạng excel!");
                        }
                        oledbcon.Close();
                        System.IO.File.Delete(path);
                    }
                    
                }
            }
            else
                mess = "Có lỗi.";

            return Json(new { State = state, Message = mess });
        }
        #endregion
        //[ChildActionOnly]
        [HttpPost]
        [ActionName("Insert")]
        public ActionResult Insert1()
        {
            return PartialView();
        }
        public ActionResult Insert()
        {
            return PartialView();
        }
        #region Phylum
        public JsonResult GetPhylum()
        {
            return Json(db.TbNganhs.ToList(), JsonRequestBehavior.AllowGet);
        }
        public ActionResult Phylum()
        {
            // Lấy danh sách khoa để tham chiếu khóa ngoại
            ViewBag.TbKhoa = db.TbKhoas.ToList();
            ViewBag.TbKhoiThi = db.TbKhoiThis.ToList();

            if (!Request.IsAjaxRequest())
                return View();

            return PartialView();
        }
        public ActionResult EditingPopup_ReadPhylum([DataSourceRequest] DataSourceRequest request)
        {
            var data = db.TbNganhs;

            return Json(data.ToDataSourceResult(request));
        }

        public ActionResult EditingPopup_CreatePhylum([DataSourceRequest] DataSourceRequest request, TbNganh data)
        {
            if (data != null && ModelState.IsValid)
            {
                db.TbNganhs.Add(data);
                db.SaveChanges();
            }
            return Json(ModelState.ToDataSourceResult());
        }
        public ActionResult EditingPopup_UpdatePhylum([DataSourceRequest] DataSourceRequest request, TbNganh data)
        {
            if (data != null && ModelState.IsValid)
            {
                db.Entry(data).State = EntityState.Modified;
                db.SaveChanges();
            }
            return Json(ModelState.ToDataSourceResult());
        }
        public ActionResult EditingPopup_DestroyPhylum([DataSourceRequest] DataSourceRequest request, TbNganh data)
        {
            if (data != null)
            {
                TbNganh itemdata = db.TbNganhs.First(item => item.MaNganh == data.MaNganh);
                db.TbNganhs.Remove(itemdata);
                db.SaveChanges();
            }
            return Json(ModelState.ToDataSourceResult());
        }
        [HttpPost]
        [ValidateAntiForgeryToken]

        public ActionResult ImportPhylum(HttpPostedFileBase FileImport)
        {

            bool state = false;
            string mess = "";
            if (FileImport != null)
            {
                // thuc hien thao tac tai day
                if (FileImport.ContentLength > 0)
                {
                    string Extension = System.IO.Path.GetExtension(FileImport.FileName);
                    if (Extension == ".xls" || Extension == ".xlsx")
                    {
                        string path = string.Concat(Server.MapPath("~/Uploads/File/" + DateTime.Now.ToString("MMddyyyyHHmmssfff") + FileImport.FileName));
                        //if (System.IO.File.Exists(path))
                        //{

                        //    System.IO.File.Delete(path);
                        //}
                        FileImport.SaveAs(path);
                        OleDbConnection oledbcon = new OleDbConnection("provider=microsoft.ace.oledb.12.0;data source=" + path + ";extended properties=excel 12.0;");
                        oledbcon.Open();
                        DataTable ExcelSheets = oledbcon.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                        string SpreadSheetName = ExcelSheets.Rows[0]["TABLE_NAME"].ToString();
                        OleDbCommand oledbcmd = new OleDbCommand("Select * from [" + SpreadSheetName + "]", oledbcon);
                        OleDbDataAdapter oledbda = new OleDbDataAdapter(oledbcmd);
                        DataTable dt = new DataTable();
                        TbNganh item;
                        oledbda.Fill(dt);
                        var ListDepartment = (from c in db.TbKhoas select c).ToList();
                        var ListGroup = (from c in db.TbKhoiThis select c).ToList();
                        var ListPhylum = (from c in db.TbNganhs select c).ToList();
                        if (dt.Rows.Count > 0)
                        {
                            if (!dt.Columns.Contains("nganh"))
                            {
                                error.Add("Cột Mã ngành không tồn tại!");
                                goto next;
                            }
                            if (!dt.Columns.Contains("tennganh"))
                            {
                                error.Add("Cột Tên ngành không tồn tại!");
                                goto next;
                            }
                            if (!dt.Columns.Contains("khoithi"))
                            {
                                error.Add("Cột Khối thi không tồn tại!");
                                goto next;
                            }
                            if (!dt.Columns.Contains("khoa"))
                            {
                                error.Add("Cột Khoa ngành không tồn tại!");
                                goto next;
                            }
                            for (int i = 0; i < dt.Rows.Count - 1; i++)
                            {
                                
                                foreach (var a in ListDepartment)
                                {
                                    CheckDepartment = false;
                                    if(a.TenKhoa.ToLower().Trim() == dt.Rows[i]["Khoa"].ToString().ToLower().Trim())
                                    {
                                        CheckDepartment = true;
                                        IDDepartment = a.MaKhoa.ToString().Trim();
                                        break;
                                    }
                                    else
                                    {
                                        error.Add("Khoa: " + dt.Rows[i]["Khoa"].ToString() + " không tồn tại!");
                                        CheckDepartment = false;
                                    }
                                }
                                foreach (var a in ListGroup)
                                {
                                    CheckGroup = false;
                                    if (a.MaKhoiThi.ToLower().Trim() == dt.Rows[i]["khoithi"].ToString().ToLower().Trim())
                                    {
                                        CheckGroup = true;
                                        break;
                                    }
                                    else
                                    {
                                        error.Add("Khối: " + dt.Rows[i]["khoithi"].ToString() + " không tồn tại!");
                                        CheckGroup = false;
                                    }
                                }
                                foreach (var a in ListPhylum)
                                {
                                    CheckPhylum = false;
                                    if (a.MaKhoiThi.ToString().ToLower().Trim() == dt.Rows[i]["khoithi"].ToString().ToLower().Trim() &&
                                        a.MaNganh.ToString().ToLower().Trim() == dt.Rows[i]["nganh"].ToString().ToLower().Trim())
                                    {
                                        CheckPhylum = true;
                                        break;
                                    }
                                }

                                //try
                                //{
                                if (CheckDepartment && CheckGroup && !CheckPhylum)
                                    {
                                        item = new TbNganh();
                                        item.MaNganh = dt.Rows[i]["nganh"].ToString().Trim();
                                        item.TenNganh = dt.Rows[i]["tennganh"].ToString().Trim();
                                        item.MaKhoa = Int32.Parse(IDDepartment);
                                        item.MaKhoiThi = dt.Rows[i]["khoithi"].ToString().Trim();
                                        db.TbNganhs.Add(item);
                                        db.SaveChanges();
                                    }
                                    
                                //}
                                //catch
                                //{
                                //}
                            }
                            for (int k = 0; k < error.Count; k++)
                            {
                                count += " + " + error[k].ToString() + "<br/>";
                            }
                        next:
                            state = true;
                            mess = "Thực hiện chèn thành công " + (dt.Rows.Count - error.Count) + " bản ghi!";
                            //empData["Message"] = "Lưu thành công!";
                        }
                        else
                        {
                            Response.Write("Bạn phải nhập file định dạng excel!");
                        }
                        oledbcon.Close();
                        System.IO.File.Delete(path);
                    }

                }
            }
            else
                mess = "Có lỗi.";

            return Json(new { State = state, Message = mess });
        }
        #endregion
        #region Major
        public JsonResult GetMajor()
        {

            return Json(db.TbChuyenNganhs.ToList(), JsonRequestBehavior.AllowGet);
        }
        public JsonResult GetMajorByPhylum(string manganh)
        {
            var major = from c in db.TbChuyenNganhs where c.IDNganh == Int32.Parse(manganh) || c.IDNganh == null
                        select c;
            return Json(new SelectList(major.ToList(), "MaChuyenNganh", "TenChuyenNganh" ), JsonRequestBehavior.AllowGet);
        }
        public ActionResult Major()
        {
            // Lấy danh sách khoa để tham chiếu khóa ngoại
            ViewBag.TbNganh = db.TbNganhs.ToList();
            ViewBag.TbKhoiThi = db.TbKhoiThis.ToList();

            if (!Request.IsAjaxRequest())
                return View();

            return PartialView();
        }
        public ActionResult EditingPopup_ReadMajor([DataSourceRequest] DataSourceRequest request)
        {
            var data = db.TbChuyenNganhs;

            return Json(data.ToDataSourceResult(request));
        }

        public ActionResult EditingPopup_CreateMajor([DataSourceRequest] DataSourceRequest request, TbChuyenNganh data)
        {
            if (data != null && ModelState.IsValid)
            {
                db.TbChuyenNganhs.Add(data);
                db.SaveChanges();
            }
            return Json(ModelState.ToDataSourceResult());
        }
        public ActionResult EditingPopup_UpdateMajor([DataSourceRequest] DataSourceRequest request, TbChuyenNganh data)
        {
            if (data != null && ModelState.IsValid)
            {
                db.Entry(data).State = EntityState.Modified;
                db.SaveChanges();
            }
            return Json(ModelState.ToDataSourceResult());
        }
        public ActionResult EditingPopup_DestroyMajor([DataSourceRequest] DataSourceRequest request, TbChuyenNganh data)
        {
            if (data != null)
            {
                TbChuyenNganh itemdata = db.TbChuyenNganhs.First(item => item.MaChuyenNganh == data.MaChuyenNganh);
                db.TbChuyenNganhs.Remove(itemdata);
                db.SaveChanges();
            }
            return Json(ModelState.ToDataSourceResult());
        }
        public ActionResult ImportMajor(HttpPostedFileBase FileImport)
        {

            bool state = false;
            string mess = "";
            if (FileImport != null)
            {
                // thuc hien thao tac tai day
                if (FileImport.ContentLength > 0)
                {
                    string Extension = System.IO.Path.GetExtension(FileImport.FileName);
                    if (Extension == ".xls" || Extension == ".xlsx")
                    {
                        string path = string.Concat(Server.MapPath("~/Uploads/File/" + DateTime.Now.ToString("MMddyyyyHHmmssfff") + FileImport.FileName));
                        //if (System.IO.File.Exists(path))
                        //{

                        //    System.IO.File.Delete(path);
                        //}
                        FileImport.SaveAs(path);
                        OleDbConnection oledbcon = new OleDbConnection("provider=microsoft.ace.oledb.12.0;data source=" + path + ";extended properties=excel 12.0;");
                        oledbcon.Open();
                        DataTable ExcelSheets = oledbcon.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                        string SpreadSheetName = ExcelSheets.Rows[0]["TABLE_NAME"].ToString();
                        OleDbCommand oledbcmd = new OleDbCommand("Select * from [" + SpreadSheetName + "]", oledbcon);
                        OleDbDataAdapter oledbda = new OleDbDataAdapter(oledbcmd);
                        DataTable dt = new DataTable();
                        TbChuyenNganh item;
                        oledbda.Fill(dt);
                        var ListPhylum = (from c in db.TbNganhs select c).ToList();
                        var ListMajor = (from c in db.TbChuyenNganhs select c).ToList();
                        if (dt.Rows.Count > 0)
                        {
                            if (!dt.Columns.Contains("Mã chuyên ngành"))
                            {
                                error.Add("Cột Mã chuyên ngành không tồn tại!");
                                goto next;
                            }
                            if (!dt.Columns.Contains("Tên chuyên ngành"))
                            {
                                error.Add("Cột Tên chuyên ngành  không tồn tại!");
                                goto next;
                            }
                            if (!dt.Columns.Contains("Ngành"))
                            {
                                error.Add("Cột Ngành không tồn tại!");
                                goto next;
                            }
                            int m = 0;
                            for (int i = 0; i < dt.Rows.Count - 1; i++)
                            {
                                //IDPhylum = "";
                                foreach (var a in ListPhylum)
                                {
                                    CheckPhylum = false;
                                    if (a.TenNganh.ToLower().Trim() == dt.Rows[i]["Ngành"].ToString().ToLower().Trim())
                                    {
                                        CheckPhylum = true;
                                        IDPhylum = a.IDNganh.ToString().Trim();
                                        //break;

                                        item = new TbChuyenNganh();
                                        item.MaChuyenNganh = (++m).ToString();
                                        item.TenChuyenNganh = dt.Rows[i]["Tên chuyên ngành"].ToString().Trim();
                                        item.IDNganh = Int32.Parse(IDPhylum);
                                        db.TbChuyenNganhs.Add(item);
                                        db.SaveChanges();
                                    }
                                    else
                                    {
                                        error.Add("Ngành: " + dt.Rows[i]["Ngành"].ToString() + " không tồn tại!");
                                        CheckPhylum = false;
                                    }
                                }
                          
                                //foreach (var a in ListMajor)
                                //{
                                //    CheckMajor = false;
                                //    if (a.MaChuyenNganh.ToLower().Trim() == dt.Rows[i]["Mã chuyên ngành"].ToString().ToLower().Trim())
                                //    {
                                //        CheckMajor = true;
                                //        error.Add("Chuyên gành: " + dt.Rows[i]["Mã chuyên ngành"].ToString() + " đã tồn tại!");
                                //        break;
                                //    }
                                //}
                                //try
                                //{
                                //if (CheckPhylum && !CheckMajor)
                                //{
                                //    item = new TbChuyenNganh();
                                //    item.MaChuyenNganh = dt.Rows[i]["Mã chuyên ngành"].ToString().Trim();
                                //    item.TenChuyenNganh = dt.Rows[i]["Tên chuyên ngành"].ToString().Trim();
                                //    item.IDNganh = Int32.Parse(IDPhylum);
                                //    db.TbChuyenNganhs.Add(item);
                                //    db.SaveChanges();
                                //}

                                //}
                                //catch
                                //{
                                //}
                            }
                            for (int k = 0; k < error.Count; k++)
                            {
                                count += " + " + error[k].ToString() + "<br/>";
                            }
                        next:
                            state = true;
                            mess = "Thực hiện chèn thành công " + (dt.Rows.Count - error.Count) + " bản ghi!";
                            //empData["Message"] = "Lưu thành công!";
                        }
                        else
                        {
                            Response.Write("Bạn phải nhập file định dạng excel!");
                        }
                        oledbcon.Close();
                        System.IO.File.Delete(path);
                    }

                }
            }
            else
                mess = "Có lỗi.";

            return Json(new { State = state, Message = mess });
        }
        #endregion
        #region Group
        public JsonResult GetGroup()
        {
            return Json(db.TbKhoiThis.ToList(), JsonRequestBehavior.AllowGet);
        }
        public ActionResult Group()
        {
            if (!Request.IsAjaxRequest())
                return View();

            return PartialView();
        }
        public ActionResult EditingPopup_ReadGroup([DataSourceRequest] DataSourceRequest request)
        {
            return Json(db.TbKhoiThis.ToDataSourceResult(request));
        }

        //[ValidateAntiForgeryToken]
        public ActionResult EditingPopup_CreateGroup([DataSourceRequest] DataSourceRequest request, TbKhoiThi data)
        {
            if (data != null && ModelState.IsValid)
            {
                db.TbKhoiThis.Add(data);
                db.SaveChanges();
            }
            return Json(ModelState.ToDataSourceResult());
        }
        public ActionResult EditingPopup_UpdateGroup([DataSourceRequest] DataSourceRequest request, TbKhoiThi data)
        {
            if (data != null && ModelState.IsValid)
            {
                db.Entry(data).State = EntityState.Modified;
                db.SaveChanges();
            }
            return Json(ModelState.ToDataSourceResult());
        }
        public ActionResult EditingPopup_DestroyGroup([DataSourceRequest] DataSourceRequest request, TbKhoiThi data)
        {
            if (data != null)
            {
                TbKhoiThi itemdata = db.TbKhoiThis.First(item => item.MaKhoiThi == data.MaKhoiThi);
                db.TbKhoiThis.Remove(itemdata);
                db.SaveChanges();
            }
            return Json(ModelState.ToDataSourceResult());
        }
        public ActionResult ImportGroup(HttpPostedFileBase FileImport)
        {

            bool state = false;
            string mess = "";
            if (FileImport != null)
            {
                // thuc hien thao tac tai day
                if (FileImport.ContentLength > 0)
                {
                    string Extension = System.IO.Path.GetExtension(FileImport.FileName);
                    if (Extension == ".xls" || Extension == ".xlsx")
                    {
                        string path = string.Concat(Server.MapPath("~/Uploads/File/" + DateTime.Now.ToString("MMddyyyyHHmmssfff") + FileImport.FileName));
                        //if (System.IO.File.Exists(path))
                        //{

                        //    System.IO.File.Delete(path);
                        //}
                        FileImport.SaveAs(path);
                        OleDbConnection oledbcon = new OleDbConnection("provider=microsoft.ace.oledb.12.0;data source=" + path + ";extended properties=excel 12.0;");
                        oledbcon.Open();
                        DataTable ExcelSheets = oledbcon.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                        string SpreadSheetName = ExcelSheets.Rows[0]["TABLE_NAME"].ToString();
                        OleDbCommand oledbcmd = new OleDbCommand("Select * from [" + SpreadSheetName + "]", oledbcon);
                        OleDbDataAdapter oledbda = new OleDbDataAdapter(oledbcmd);

                        DataTable dt = new DataTable();
                        var khoithi = db.TbKhoiThis.Select(m => m.MaKhoiThi);
                        TbKhoiThi item;
                        oledbda.Fill(dt);
                        if (dt.Rows.Count > 0)
                        {
                            if (!dt.Columns.Contains("Mã khối thi"))
                            {
                                error.Add("Cột Mã khối thi không tồn tại!");
                                goto next;
                            }
                            if (!dt.Columns.Contains("Tên khối thi"))
                            {
                                error.Add("Cột Tên khối thi không tồn tại!");
                                goto next;
                            }
                            for (int i = 0; i < dt.Rows.Count - 1; i++)
                            {
                                foreach (var a in khoithi)
                                {
                                    CheckGroup = false;
                                    if (a.ToString().ToLower().Trim() == dt.Rows[i]["Mã khối thi"].ToString().ToLower().Trim())
                                    {
                                        CheckGroup = true;
                                        break;
                                    }
                                }

                                //try
                                //{
                                if(!CheckGroup)
                                {
                                    item = new TbKhoiThi();
                                    item.MaKhoiThi = dt.Rows[i]["Mã khối thi"].ToString().Trim();
                                    item.TenKhoiThi = dt.Rows[i]["Tên khối thi"].ToString().Trim();
                                    db.TbKhoiThis.Add(item);
                                    db.SaveChanges();
                                }
                                else
                                {
                                    error.Add("Khối thi: " + dt.Rows[i]["Mã khối thi"].ToString() + " đã tồn tại!");
                                }
                                //}
                                //catch
                                //{
                                //}
                            }
                            for (int k = 0; k < error.Count; k++)
                            {
                                count += " + " + error[k].ToString() + "<br/>";
                            }
                        next:
                            state = true;
                            mess = "Thực hiện chèn thành công " + (dt.Rows.Count - error.Count) + " bản ghi!";
                        }
                        else
                        {
                            Response.Write("Bạn phải nhập file định dạng excel!");
                        }
                        oledbcon.Close();
                        System.IO.File.Delete(path);
                    }

                }
            }
            else
                mess = "Có lỗi.";

            return Json(new { State = state, Message = mess });
        }
        #endregion
        #region Area
        public JsonResult GetArea()
        {
            return Json(db.TbKhuVucs.ToList(), JsonRequestBehavior.AllowGet);
        }
        public ActionResult Area()
        {
            if (!Request.IsAjaxRequest())
                return View();

            return PartialView();
        }
        public ActionResult EditingPopup_ReadArea([DataSourceRequest] DataSourceRequest request)
        {
            return Json(db.TbKhuVucs.ToDataSourceResult(request));
        }

        //[ValidateAntiForgeryToken]
        public ActionResult EditingPopup_CreateArea([DataSourceRequest] DataSourceRequest request, TbKhuVuc data)
        {
            var x = db.TbKhuVucs;
            if (x.Where(m => m.MaKhuVuc == data.MaKhuVuc).Count() == 0)
            {
                if (data != null && ModelState.IsValid)
                {
                    db.TbKhuVucs.Add(data);
                    db.SaveChanges();
                }
            }
            else
            {
                return View("Area");
            }
            
            return Json(ModelState.ToDataSourceResult());
        }
        public ActionResult EditingPopup_UpdateArea([DataSourceRequest] DataSourceRequest request, TbKhuVuc data)
        {
            if (data != null && ModelState.IsValid)
            {
                db.Entry(data).State = EntityState.Modified;
                db.SaveChanges();
            }
            return Json(ModelState.ToDataSourceResult());
        }
        public ActionResult EditingPopup_DestroyArea([DataSourceRequest] DataSourceRequest request, TbKhuVuc data)
        {
            if (data != null)
            {
                TbKhuVuc itemdata = db.TbKhuVucs.First(item => item.MaKhuVuc == data.MaKhuVuc);
                db.TbKhuVucs.Remove(itemdata);
                db.SaveChanges();
            }
            return Json(ModelState.ToDataSourceResult());
        }
        public ActionResult ImportArea(HttpPostedFileBase FileImport)
        {

            bool state = false;
            string mess = "";
            if (FileImport != null)
            {
                // thuc hien thao tac tai day
                if (FileImport.ContentLength > 0)
                {
                    string Extension = System.IO.Path.GetExtension(FileImport.FileName);
                    if (Extension == ".xls" || Extension == ".xlsx")
                    {
                        string path = string.Concat(Server.MapPath("~/Uploads/File/" + DateTime.Now.ToString("MMddyyyyHHmmssfff") + FileImport.FileName));
                        //if (System.IO.File.Exists(path))
                        //{

                        //    System.IO.File.Delete(path);
                        //}
                        FileImport.SaveAs(path);
                        OleDbConnection oledbcon = new OleDbConnection("provider=microsoft.ace.oledb.12.0;data source=" + path + ";extended properties=excel 12.0;");
                        oledbcon.Open();
                        DataTable ExcelSheets = oledbcon.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                        string SpreadSheetName = ExcelSheets.Rows[0]["TABLE_NAME"].ToString();
                        OleDbCommand oledbcmd = new OleDbCommand("Select * from [" + SpreadSheetName + "]", oledbcon);
                        OleDbDataAdapter oledbda = new OleDbDataAdapter(oledbcmd);

                        DataTable dt = new DataTable();
                        TbKhuVuc item;
                        var Kv = db.TbKhuVucs.Select(m => m.MaKhuVuc);
                        oledbda.Fill(dt);
                        if (dt.Rows.Count > 0)
                        {
                            if (!dt.Columns.Contains("Mã khu vực"))
                            {
                                error.Add("Cột Mã khu vực không tồn tại!");
                                goto next;
                            }
                            if (!dt.Columns.Contains("Tên khu vực"))
                            {
                                error.Add("Cột Tên khu vực không tồn tại!");
                                goto next;
                            }
                            for (int i = 0; i < dt.Rows.Count - 1; i++)
                            {
                                foreach (var a in Kv)
                                {
                                    CheckArea = false;
                                    if(a.ToString().ToLower().Trim() == dt.Rows[i]["Mã khu vực"].ToString().ToLower().Trim())
                                    {
                                        CheckArea = true;
                                        break;
                                    }
                                }
                                //try
                                //{
                                if(!CheckArea)
                                {
                                    item = new TbKhuVuc();
                                    item.MaKhuVuc = dt.Rows[i]["Mã khu vực"].ToString().Trim();
                                    item.TenKhuVuc = dt.Rows[i]["Tên khu vực"].ToString().Trim();
                                    db.TbKhuVucs.Add(item);
                                    db.SaveChanges();
                                }
                                else
                                {
                                    error.Add("Khu vực: " + dt.Rows[i]["Mã khu vực"].ToString() + " đã tồn tại!");
                                }
                                //}
                                //catch
                                //{
                                //}
                            }
                            for (int k = 0; k < error.Count; k++)
                            {
                                count += " + " + error[k].ToString() + "<br/>";
                            }
                        next:
                           // Response.Write(count);
                            state = true;
                            mess = "Thực hiện chèn thành công " + (dt.Rows.Count - error.Count) + " bản ghi!";
                            //empData["Message"] = "Lưu thành công!";
                        }
                        else
                        {
                            Response.Write("Bạn phải nhập file định dạng excel!");
                        }
                        oledbcon.Close();
                        System.IO.File.Delete(path);
                    }

                }
            }
            else
                mess = "Có lỗi.";

            return Json(new { State = state, Message = mess });
        }
        //[HttpPost]
        public ActionResult ExportArea(string contentType, string base64, string fileName)
        {
            var fileContents = Convert.FromBase64String(base64);

            return File(fileContents, contentType, fileName);
        }
        #endregion
        #region Ethnic
        public JsonResult GetEthnic()
        {
            return Json(db.TbDanTocs.ToList(), JsonRequestBehavior.AllowGet);
        }
        public ActionResult Ethnic()
        {
            if (!Request.IsAjaxRequest())
                return View();

            return PartialView();
        }
        public ActionResult EditingPopup_ReadEthnic([DataSourceRequest] DataSourceRequest request)
        {
            return Json(db.TbDanTocs.ToDataSourceResult(request));
        }

        //[ValidateAntiForgeryToken]
        public ActionResult EditingPopup_CreateEthnic([DataSourceRequest] DataSourceRequest request, TbDanToc data)
        {
            if (data != null && ModelState.IsValid)
            {
                db.TbDanTocs.Add(data);
                db.SaveChanges();
            }
            return Json(ModelState.ToDataSourceResult());
        }
        public ActionResult EditingPopup_UpdateEthnic([DataSourceRequest] DataSourceRequest request, TbDanToc data)
        {
            if (data != null && ModelState.IsValid)
            {
                db.Entry(data).State = EntityState.Modified;
                db.SaveChanges();
            }
            return Json(ModelState.ToDataSourceResult());
        }
        public ActionResult EditingPopup_DestroyEthnic([DataSourceRequest] DataSourceRequest request, TbDanToc data)
        {
            if (data != null)
            {
                TbDanToc itemdata = db.TbDanTocs.First(item => item.MaDanToc == data.MaDanToc);
                db.TbDanTocs.Remove(itemdata);
                db.SaveChanges();
            }
            return Json(ModelState.ToDataSourceResult());
        }
        public ActionResult ImportEthnic(HttpPostedFileBase FileImport)
        {

            bool state = false;
            string mess = "";
            if (FileImport != null)
            {
                // thuc hien thao tac tai day
                if (FileImport.ContentLength > 0)
                {
                    string Extension = System.IO.Path.GetExtension(FileImport.FileName);
                    if (Extension == ".xls" || Extension == ".xlsx")
                    {
                        string path = string.Concat(Server.MapPath("~/Uploads/File/" + DateTime.Now.ToString("MMddyyyyHHmmssfff") + FileImport.FileName));
                        //if (System.IO.File.Exists(path))
                        //{

                        //    System.IO.File.Delete(path);
                        //}
                        FileImport.SaveAs(path);
                        OleDbConnection oledbcon = new OleDbConnection("provider=microsoft.ace.oledb.12.0;data source=" + path + ";extended properties=excel 12.0;");
                        oledbcon.Open();
                        DataTable ExcelSheets = oledbcon.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                        string SpreadSheetName = ExcelSheets.Rows[0]["TABLE_NAME"].ToString();
                        OleDbCommand oledbcmd = new OleDbCommand("Select * from [" + SpreadSheetName + "]", oledbcon);
                        OleDbDataAdapter oledbda = new OleDbDataAdapter(oledbcmd);

                        DataTable dt = new DataTable();
                        TbDanToc item;
                        var ethnic = db.TbDanTocs.Select(m => m.TenDanToc);
                        oledbda.Fill(dt);
                        if (dt.Rows.Count > 0)
                        {
                            if (!dt.Columns.Contains("Dân tộc"))
                            {
                                error.Add("Cột Dân tộc không tồn tại!");
                                goto next;
                            }
                            for (int i = 0; i < dt.Rows.Count - 1; i++)
                            {
                                foreach (var a in ethnic)
                                {
                                    CheckEthnic = false;
                                    if (a.ToString().ToLower().Trim() == dt.Rows[i]["Dân tộc"].ToString().ToLower().Trim())
                                    {
                                        CheckEthnic = true;
                                        break;
                                    }
                                }
                                //try
                                //{
                                if(!CheckEthnic)
                                {
                                    item = new TbDanToc();
                                    item.TenDanToc = dt.Rows[i]["Dân tộc"].ToString().Trim();
                                    //item.MoTa = dt.Rows[i]["Mô tả"].ToString().Trim();
                                    db.TbDanTocs.Add(item);
                                    db.SaveChanges();
                                 }
                                else
                                {
                                    error.Add("Dân tộc: " + dt.Rows[i]["Dân tộc"].ToString() + " đã tồn tại!");
                                }   
                                //}
                                //catch
                                //{
                                //}
                            }
                        for (int k = 0; k < error.Count; k++)
                            {
                                count += " + " + error[k].ToString() + "<br/>";
                            }
                        next:
                            state = true;
                            mess = "Thực hiện chèn thành công " + (dt.Rows.Count - error.Count) + " bản ghi!";
                            //empData["Message"] = "Lưu thành công!";
                        }
                        else
                        {
                            Response.Write("Bạn phải nhập file định dạng excel!");
                        }
                        oledbcon.Close();
                        System.IO.File.Delete(path);
                    }

                }
            }
            else
                mess = "Có lỗi.";

            return Json(new { State = state, Message = mess });
        }
        #endregion
        #region Province
        //[OutputCache(Duration=600)]
        public JsonResult GetProvince()
        {
            return Json(db.TbTinhs.ToList(), JsonRequestBehavior.AllowGet);
        }
        public ActionResult Province()
        {
            if (!Request.IsAjaxRequest())
                return View();

            return PartialView();
        }
        public ActionResult EditingPopup_ReadProvince([DataSourceRequest] DataSourceRequest request)
        {
            return Json(db.TbTinhs.ToDataSourceResult(request));
        }

        public ActionResult EditingPopup_CreateProvince([DataSourceRequest] DataSourceRequest request, TbTinh data)
        {
            if (data != null && ModelState.IsValid)
            {
                db.TbTinhs.Add(data);
                db.SaveChanges();
            }
            return Json(ModelState.ToDataSourceResult());
        }

        public ActionResult EditingPopup_UpdateProvince([DataSourceRequest] DataSourceRequest request, TbTinh data)
        {
            if (data != null && ModelState.IsValid)
            {
                db.Entry(data).State = EntityState.Modified;
                db.SaveChanges();
            }
            return Json(ModelState.ToDataSourceResult());
        }
        public ActionResult EditingPopup_DestroyProvince([DataSourceRequest] DataSourceRequest request, TbTinh data)
        {
            if (data != null)
            {
                TbTinh itemdata = db.TbTinhs.First(item => item.MaTinh == data.MaTinh);
                db.TbTinhs.Remove(itemdata);
                db.SaveChanges();
            }
            return Json(ModelState.ToDataSourceResult());
        }

        [HttpPost]
        [ValidateAntiForgeryToken]
        
        public ActionResult ImportProvince(HttpPostedFileBase FileImport)
        {
            
            bool state = false;
            string mess = "";
            if (FileImport != null)
            {
                // thuc hien thao tac tai day
                if (FileImport.ContentLength > 0)
                {
                    string Extension = System.IO.Path.GetExtension(FileImport.FileName);
                    if (Extension == ".xls" || Extension == ".xlsx")
                    {
                        string path = string.Concat(Server.MapPath("~/Uploads/File/" + DateTime.Now.ToString("MMddyyyyHHmmssfff") + FileImport.FileName));
                        //if (System.IO.File.Exists(path))
                        //{

                        //    System.IO.File.Delete(path);
                        //}
                        FileImport.SaveAs(path);
                        OleDbConnection oledbcon = new OleDbConnection("provider=microsoft.ace.oledb.12.0;data source=" + path + ";extended properties=excel 12.0;");
                        oledbcon.Open();
                        DataTable ExcelSheets = oledbcon.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                        string SpreadSheetName = ExcelSheets.Rows[0]["TABLE_NAME"].ToString();
                        OleDbCommand oledbcmd = new OleDbCommand("Select * from [" + SpreadSheetName + "]", oledbcon);
                        OleDbDataAdapter oledbda = new OleDbDataAdapter(oledbcmd);

                        DataTable dt = new DataTable();
                        TbTinh item;
                        var province = db.TbTinhs.Select(m => m.MaTinh);
                        oledbda.Fill(dt);
                        if (dt.Rows.Count > 0)
                        {
                            if (!dt.Columns.Contains("Mã tỉnh"))
                            {
                                error.Add("Cột Mã tỉnh không tồn tại!");
                                goto next;
                            }
                            if (!dt.Columns.Contains("Tên tỉnh"))
                            {
                                error.Add("Cột Tên tỉnh không tồn tại!");
                                goto next;
                            }
                            for (int i = 0; i < dt.Rows.Count - 1; i++)
                            {
                                foreach (var a in province)
                                {
                                    CheckProvince = false;
                                    if (a.ToString().ToLower().Trim() == dt.Rows[i]["Mã tỉnh"].ToString().ToLower().Trim())
                                    {
                                        CheckProvince = true;
                                        break;
                                    }
                                }
                                //try
                                //{
                                if(!CheckProvince)
                                {
                                    item = new TbTinh();
                                    item.MaTinh = dt.Rows[i]["Mã Tỉnh"].ToString().Trim();
                                    item.TenTinh = dt.Rows[i]["Tên Tỉnh"].ToString().Trim();
                                    db.TbTinhs.Add(item);
                                    db.SaveChanges();
                                }
                                else
                                {
                                    error.Add("Tỉnh/TP: " + dt.Rows[i]["Tên tỉnh"].ToString() + " đã tồn tại!");
                                }
                                //}
                                //catch
                                //{                                                                     
                                //}                            
                            }
                            for (int k = 0; k < error.Count; k++)
                            {
                                count += " + " + error[k].ToString() + "<br/>";
                            }
                        next:

                            state = true;
                            mess = "Thực hiện chèn thành công " + (dt.Rows.Count - error.Count) + " bản ghi!";
                            //empData["Message"] = "Lưu thành công!";
                        }
                        else
                        {
                            Response.Write("Bạn phải nhập file định dạng excel!");
                        }
                        oledbcon.Close();
                        System.IO.File.Delete(path);
                    }
                    
                }               
            }
            else
                mess = "Có lỗi.";

            return Json(new {State=state,Message=mess});
        }
        #endregion
        #region Commune

        public ActionResult Commune()
        {
            Database.SetInitializer<TS2015Context>(null);
            // Lấy danh sách khoa để tham chiếu khóa ngoại
            ViewBag.TbTinh = db.TbTinhs.ToList();

            if (!Request.IsAjaxRequest())
                return View();

            return PartialView();
        }
        public ActionResult EditingPopup_ReadCommune([DataSourceRequest] DataSourceRequest request)
        {
            var data = db.TbHuyens;

            return Json(data.ToDataSourceResult(request));
        }

        public ActionResult EditingPopup_CreateCommune([DataSourceRequest] DataSourceRequest request, TbHuyen data)
        {
            if (data != null && ModelState.IsValid)
            {
                db.TbHuyens.Add(data);
                db.SaveChanges();
            }
            return Json(ModelState.ToDataSourceResult());
        }
        public ActionResult EditingPopup_UpdateCommnue([DataSourceRequest] DataSourceRequest request, TbHuyen data)
        {
            if (data != null && ModelState.IsValid)
            {
                db.Entry(data).State = EntityState.Modified;
                db.SaveChanges();
            }
            return Json(ModelState.ToDataSourceResult());
        }
        public ActionResult EditingPopup_DestroyCommune([DataSourceRequest] DataSourceRequest request, TbHuyen data)
        {
            if (data != null)
            {
                TbHuyen itemdata = db.TbHuyens.First(item => item.MaHuyen == data.MaHuyen);
                db.TbHuyens.Remove(itemdata);
                db.SaveChanges();
            }
            return Json(ModelState.ToDataSourceResult());
        }
        
        [HttpPost]
        [ValidateAntiForgeryToken]

        public ActionResult ImportCommune(HttpPostedFileBase FileImport)
        {

            bool state = false;
            string mess = "";
            if (FileImport != null)
            {
                // thuc hien thao tac tai day
                if (FileImport.ContentLength > 0)
                {
                    string Extension = System.IO.Path.GetExtension(FileImport.FileName);
                    if (Extension == ".xls" || Extension == ".xlsx")
                    {
                        string path = string.Concat(Server.MapPath("~/Uploads/File/" + DateTime.Now.ToString("MMddyyyyHHmmssfff") + FileImport.FileName));
                        //if (System.IO.File.Exists(path))
                        //{

                        //    System.IO.File.Delete(path);
                        //}
                        FileImport.SaveAs(path);
                        OleDbConnection oledbcon = new OleDbConnection("provider=microsoft.ace.oledb.12.0;data source=" + path + ";extended properties=excel 12.0;");
                        oledbcon.Open();
                        DataTable ExcelSheets = oledbcon.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                        string SpreadSheetName = ExcelSheets.Rows[0]["TABLE_NAME"].ToString();
                        OleDbCommand oledbcmd = new OleDbCommand("Select * from [" + SpreadSheetName + "]", oledbcon);
                        OleDbDataAdapter oledbda = new OleDbDataAdapter(oledbcmd);
                        DataTable dt = new DataTable();
                        TbHuyen item;
                        oledbda.Fill(dt);
                        var ListProvince = (from c in db.TbTinhs select c).ToList();
                        var ListCommune = db.TbHuyens.Select(m => m.MaHuyen);
                        if (dt.Rows.Count > 0)
                        {
                            if (!dt.Columns.Contains("Mã huyện"))
                            {
                                error.Add("Cột Mã huyện không tồn tại!");
                                goto next;
                            }
                            if (!dt.Columns.Contains("Tên huyện"))
                            {
                                error.Add("Cột Tên huyện không tồn tại!");
                                goto next;
                            }
                            if (!dt.Columns.Contains("Mã tỉnh"))
                            {
                                error.Add("Cột Mã tỉnh không tồn tại!");
                                goto next;
                            }
                            for (int i = 0; i < dt.Rows.Count - 1; i++)
                            {

                                foreach (var a in ListProvince)
                                {
                                    CheckProvince = false;
                                    if (a.MaTinh.ToLower().Trim() == dt.Rows[i]["Mã tỉnh"].ToString().ToLower().Trim())
                                    {
                                        CheckProvince = true;
                                        break;
                                    }
                                }
                                foreach (var b in ListCommune)
                                {
                                    CheckCommune = false;
                                    if (b.ToLower().Trim() == dt.Rows[i]["Mã huyện"].ToString().ToLower().Trim())
                                    {
                                        CheckCommune = true;
                                        break;
                                    }
                                }

                                    //try
                                    //{
                                    if (CheckProvince && !CheckCommune)
                                    {
                                        item = new TbHuyen();
                                        item.MaHuyen = dt.Rows[i]["Mã huyện"].ToString().Trim();
                                        item.TenHuyen = dt.Rows[i]["Tên huyện"].ToString().Trim();
                                        item.MaTinh = dt.Rows[i]["Mã tỉnh"].ToString().Trim();
                                        db.TbHuyens.Add(item);
                                        db.SaveChanges();
                                    }

                                    //}
                                    //catch
                                    //{
                                    //}
                            }
                            for (int k = 0; k < error.Count; k++)
                            {
                                count += " + " + error[k].ToString() + "<br/>";
                            }
                        next:
                            state = true;
                            mess = "Thực hiện chèn thành công " + (dt.Rows.Count - error.Count) + " bản ghi!";
                            //empData["Message"] = "Lưu thành công!";
                        }
                        else
                        {
                            Response.Write("Bạn phải nhập file định dạng excel!");
                        }
                        oledbcon.Close();
                        System.IO.File.Delete(path);
                    }

                }
            }
            else
                mess = "Có lỗi.";

            return Json(new { State = state, Message = mess });
        }
        #endregion
        #region PriorityGroup
        public JsonResult GetPriorityGroup()
        {
            return Json(db.TbDoiTuongUuTiens.ToList(), JsonRequestBehavior.AllowGet);
        }
        public ActionResult PriorityGroup()
        {
            if (!Request.IsAjaxRequest())
                return View();

            return PartialView();
        }
        public ActionResult EditingPopup_ReadPriorityGroup([DataSourceRequest] DataSourceRequest request)
        {
            return Json(db.TbDoiTuongUuTiens.ToDataSourceResult(request));
        }

        //[ValidateAntiForgeryToken]
        public ActionResult EditingPopup_CreatePriorityGroup([DataSourceRequest] DataSourceRequest request, TbDoiTuongUuTien data)
        {
            if (data != null && ModelState.IsValid)
            {
                db.TbDoiTuongUuTiens.Add(data);
                db.SaveChanges();
            }
            return Json(ModelState.ToDataSourceResult());
        }
        public ActionResult EditingPopup_UpdatePriorityGroup([DataSourceRequest] DataSourceRequest request, TbDoiTuongUuTien data)
        {
            if (data != null && ModelState.IsValid)
            {
                db.Entry(data).State = EntityState.Modified;
                db.SaveChanges();
            }
            return Json(ModelState.ToDataSourceResult());
        }
        public ActionResult EditingPopup_DestroyPriorityGroup([DataSourceRequest] DataSourceRequest request, TbDoiTuongUuTien data)
        {
            if (data != null)
            {
                TbDoiTuongUuTien itemdata = db.TbDoiTuongUuTiens.First(item => item.MaDoiTuong == data.MaDoiTuong);
                db.TbDoiTuongUuTiens.Remove(itemdata);
                db.SaveChanges();
            }
            return Json(ModelState.ToDataSourceResult());
        }
        public ActionResult ImportPriorityGroup(HttpPostedFileBase FileImport)
        {

            bool state = false;
            string mess = "";
            if (FileImport != null)
            {
                // thuc hien thao tac tai day
                if (FileImport.ContentLength > 0)
                {
                    string Extension = System.IO.Path.GetExtension(FileImport.FileName);
                    if (Extension == ".xls" || Extension == ".xlsx")
                    {
                        string path = string.Concat(Server.MapPath("~/Uploads/File/" + DateTime.Now.ToString("MMddyyyyHHmmssfff") + FileImport.FileName));
                        //if (System.IO.File.Exists(path))
                        //{

                        //    System.IO.File.Delete(path);
                        //}
                        FileImport.SaveAs(path);
                        OleDbConnection oledbcon = new OleDbConnection("provider=microsoft.ace.oledb.12.0;data source=" + path + ";extended properties=excel 12.0;");
                        oledbcon.Open();
                        DataTable ExcelSheets = oledbcon.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                        string SpreadSheetName = ExcelSheets.Rows[0]["TABLE_NAME"].ToString();
                        OleDbCommand oledbcmd = new OleDbCommand("Select * from [" + SpreadSheetName + "]", oledbcon);
                        OleDbDataAdapter oledbda = new OleDbDataAdapter(oledbcmd);
                        var ListPriorityGroup = db.TbDoiTuongUuTiens.Select(m => m.TenDoiTuong);
                        DataTable dt = new DataTable();
                        TbDoiTuongUuTien item;
                        oledbda.Fill(dt);
                        if (dt.Rows.Count > 0)
                        {
                            if (!dt.Columns.Contains("Tên đối tượng ưu tiên"))
                            {
                                error.Add("Cột Tên đối tượng ưu tiên không tồn tại!");
                                goto next;
                            }
                            if (!dt.Columns.Contains("Nhóm"))
                            {
                                error.Add("Cột Nhóm không tồn tại!");
                                goto next;
                            }
                            for (int i = 0; i < dt.Rows.Count - 1; i++)
                            {
                                foreach (var b in ListPriorityGroup)
                                {
                                    CheckPriorityGroup = false;
                                    if (b.ToLower().Trim() == dt.Rows[i]["Tên đối tượng ưu tiên"].ToString().ToLower().Trim())
                                    {
                                        CheckPriorityGroup = true;
                                        break;
                                    }
                                }
                                //try
                                //{
                                if (!CheckPriorityGroup)
                                {
                                    item = new TbDoiTuongUuTien();
                                    item.TenDoiTuong = dt.Rows[i]["Tên đối tượng ưu tiên"].ToString().Trim();
                                    if (dt.Rows[i]["Nhóm"].ToString().ToLower().Trim() == "Nhóm 1".ToLower().Trim() || dt.Rows[i]["Nhóm"].ToString().ToLower().Trim() == "1".ToLower().Trim())
                                    {
                                        item.NhomDoiTuong = NhomDoiTuong.Nhom1;
                                    }
                                    else
                                    {
                                        item.NhomDoiTuong = NhomDoiTuong.Nhom2;
                                    }
                                    db.TbDoiTuongUuTiens.Add(item);
                                    db.SaveChanges();
                                }
                                else
                                {
                                    error.Add("Đối tượng: " + dt.Rows[i]["Tên đối tượng ưu tiên"].ToString() + " đã tồn tại!");
                                }
                                //}
                                //catch
                                //{
                                //}
                            }
                            for (int k = 0; k < error.Count; k++)
                            {
                                count += " + " + error[k].ToString() + "<br/>";
                            }
                        next:
                            state = true;
                            mess = "Thực hiện chèn thành công " + (dt.Rows.Count - error.Count) + " bản ghi!";
                            //empData["Message"] = "Lưu thành công!";
                        }
                        else
                        {
                            Response.Write("Bạn phải nhập file định dạng excel!");
                        }
                        oledbcon.Close();
                        System.IO.File.Delete(path);
                    }

                }
            }
            else
                mess = "Có lỗi.";

            return Json(new { State = state, Message = mess });
        }
        #endregion
        #region GraduateSubject
        public JsonResult GetGraduateSubject()
        {
            return Json(db.TbMonTotNghieps.ToList(), JsonRequestBehavior.AllowGet);
        }
        public ActionResult GraduateSubject()
        {
            if (!Request.IsAjaxRequest())
                return View();

            return PartialView();
        }
        public ActionResult EditingPopup_ReadGraduateSubject([DataSourceRequest] DataSourceRequest request)
        {
            return Json(db.TbMonTotNghieps.ToDataSourceResult(request));
        }

        //[ValidateAntiForgeryToken]
        public ActionResult EditingPopup_CreateGraduateSubject([DataSourceRequest] DataSourceRequest request, TbMonTotNghiep data)
        {
            if (data != null && ModelState.IsValid)
            {
                db.TbMonTotNghieps.Add(data);
                db.SaveChanges();
            }
            return Json(ModelState.ToDataSourceResult());
        }
        public ActionResult EditingPopup_UpdateGraduateSubject([DataSourceRequest] DataSourceRequest request, TbMonTotNghiep data)
        {
            if (data != null && ModelState.IsValid)
            {
                db.Entry(data).State = EntityState.Modified;
                db.SaveChanges();
            }
            return Json(ModelState.ToDataSourceResult());
        }
        public ActionResult EditingPopup_DestroyGraduateSubject([DataSourceRequest] DataSourceRequest request, TbMonTotNghiep data)
        {
            if (data != null)
            {
                TbMonTotNghiep itemdata = db.TbMonTotNghieps.First(item => item.MaMon == data.MaMon);
                db.TbMonTotNghieps.Remove(itemdata);
                db.SaveChanges();
            }
            return Json(ModelState.ToDataSourceResult());
        }
        public ActionResult ImportGraduteSubject(HttpPostedFileBase FileImport)
        {

            bool state = false;
            string mess = "";
            if (FileImport != null)
            {
                // thuc hien thao tac tai day
                if (FileImport.ContentLength > 0)
                {
                    string Extension = System.IO.Path.GetExtension(FileImport.FileName);
                    if (Extension == ".xls" || Extension == ".xlsx")
                    {
                        string path = string.Concat(Server.MapPath("~/Uploads/File/" + FileImport.FileName));
                        //if (System.IO.File.Exists(path))
                        //{

                        //    System.IO.File.Delete(path);
                        //}
                        FileImport.SaveAs(path);
                        OleDbConnection oledbcon = new OleDbConnection("provider=microsoft.ace.oledb.12.0;data source=" + path + ";extended properties=excel 12.0;");
                        oledbcon.Open();
                        DataTable ExcelSheets = oledbcon.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                        string SpreadSheetName = ExcelSheets.Rows[0]["TABLE_NAME"].ToString();
                        OleDbCommand oledbcmd = new OleDbCommand("Select * from [" + SpreadSheetName + "]", oledbcon);
                        OleDbDataAdapter oledbda = new OleDbDataAdapter(oledbcmd);

                        DataTable dt = new DataTable();
                        var ListGraduateSubject = db.TbMonTotNghieps.Select(m => m.TenMon);
                        TbMonTotNghiep item;
                        oledbda.Fill(dt);
                        if (dt.Rows.Count > 0)
                        {
                            if (!dt.Columns.Contains("Tên môn"))
                            {
                                error.Add("Cột Tên môn không tồn tại!");
                                goto next;
                            }
                            for (int i = 0; i < dt.Rows.Count - 1; i++)
                            {
                                foreach (var b in ListGraduateSubject)
                                {
                                    CheckGraduateSubject = false;
                                    if (b.ToLower().Trim() == dt.Rows[i]["Tên môn"].ToString().ToLower().Trim())
                                    {
                                        CheckGraduateSubject = true;
                                        break;
                                    }
                                }
                                //try
                                //{
                                if (!CheckGraduateSubject)
                                {
                                    item = new TbMonTotNghiep();
                                    //item.MaMon = dt.Rows[i]["Mã Tỉnh"].ToString().Trim();
                                    item.TenMon = dt.Rows[i]["Tên môn"].ToString().Trim();
                                    db.TbMonTotNghieps.Add(item);
                                    db.SaveChanges();
                                }
                                else
                                {
                                    error.Add("Môn: " + dt.Rows[i]["Tên môn"].ToString() + " đã tồn tại!");
                                }
                                //}
                                //catch
                                //{
                                //}
                            }
                            for (int k = 0; k < error.Count; k++)
                            {
                                count += " + " + error[k].ToString() + "<br/>";
                            }
                        next:
                            state = true;
                            mess = "Thực hiện chèn thành công " + (dt.Rows.Count - error.Count) + " bản ghi!";
                            //empData["Message"] = "Lưu thành công!";
                        }
                        else
                        {
                            Response.Write("Bạn phải nhập file định dạng excel!");
                        }
                        oledbcon.Close();
                        System.IO.File.Delete(path);
                    }

                }
            }
            else
                mess = "Có lỗi.";
            return Json(new { State = state, Message = mess });
        }
        #endregion  
        #region GraduateSubject_Group
        public JsonResult GetGraduateSubject_Group()
        {
            return Json(db.TbNganhs.ToList(), JsonRequestBehavior.AllowGet);
        }
        public ActionResult GraduateSubject_Group()
        {
            // Lấy danh sách khoa để tham chiếu khóa ngoại
            ViewBag.TbMonTotNghiep = db.TbMonTotNghieps.ToList();
            ViewBag.TbKhoiThi = db.TbKhoiThis.ToList();

            if (!Request.IsAjaxRequest())
                return View();

            return PartialView();
        }
        public ActionResult EditingPopup_ReadGraduateSubject_Group([DataSourceRequest] DataSourceRequest request)
        {
            var data = db.TbKhoiThi_MonTotNghieps;

            return Json(data.ToDataSourceResult(request));
        }
        public ActionResult EditingPopup_CreateGraduateSubject_Group([DataSourceRequest] DataSourceRequest request, TbKhoiThi_MonTotNghiep data)
        {
            if (data != null && ModelState.IsValid)
            {
                db.TbKhoiThi_MonTotNghieps.Add(data);
                SaveChanges(db);
            }
            return Json(ModelState.ToDataSourceResult());
        }
        public ActionResult EditingPopup_UpdateGraduateSubject_Group([DataSourceRequest] DataSourceRequest request, TbKhoiThi_MonTotNghiep data)
        {
            if (data != null && ModelState.IsValid)
            {
                db.Entry(data).State = EntityState.Modified;
                db.SaveChanges();
            }
            return Json(ModelState.ToDataSourceResult());
        }
        public ActionResult EditingPopup_DestroyGraduateSubject_Group([DataSourceRequest] DataSourceRequest request, TbKhoiThi_MonTotNghiep data)
        {
            if (data != null)
            {
                TbKhoiThi_MonTotNghiep itemdata = db.TbKhoiThi_MonTotNghieps.First(item => item.MaMon == data.MaMon && item.MaKhoiThi == data.MaKhoiThi);
                db.TbKhoiThi_MonTotNghieps.Remove(itemdata);
                db.SaveChanges();
            }
            return Json(ModelState.ToDataSourceResult());
        }
        [HttpPost]
        [ValidateAntiForgeryToken]

        public ActionResult ImportGraduateSubject_Group(HttpPostedFileBase FileImport)
        {

            bool state = false;
            string mess = "";
            if (FileImport != null)
            {
                // thuc hien thao tac tai day
                if (FileImport.ContentLength > 0)
                {
                    string Extension = System.IO.Path.GetExtension(FileImport.FileName);
                    if (Extension == ".xls" || Extension == ".xlsx")
                    {
                        string path = string.Concat(Server.MapPath("~/Uploads/File/" + DateTime.Now.ToString("MMddyyyyHHmmssfff") + FileImport.FileName));
                        //if (System.IO.File.Exists(path))
                        //{

                        //    System.IO.File.Delete(path);
                        //}
                        FileImport.SaveAs(path);
                        OleDbConnection oledbcon = new OleDbConnection("provider=microsoft.ace.oledb.12.0;data source=" + path + ";extended properties=excel 12.0;");
                        oledbcon.Open();
                        DataTable ExcelSheets = oledbcon.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                        string SpreadSheetName = ExcelSheets.Rows[0]["TABLE_NAME"].ToString();
                        OleDbCommand oledbcmd = new OleDbCommand("Select * from [" + SpreadSheetName + "]", oledbcon);
                        OleDbDataAdapter oledbda = new OleDbDataAdapter(oledbcmd);
                        DataTable dt = new DataTable();
                        TbKhoiThi_MonTotNghiep item;
                        oledbda.Fill(dt);
                        var ListGraduateSubject = (from c in db.TbMonTotNghieps select c).ToList();
                        var ListGroup = (from c in db.TbKhoiThis select c).ToList();
                        var ListGraduateSubject_Group = (from c in db.TbKhoiThi_MonTotNghieps select c).ToList();
                        if (dt.Rows.Count > 0)
                        {
                            if (!dt.Columns.Contains("Môn thi"))
                            {
                                error.Add("Cột Môn thi không tồn tại!");
                                goto next;
                            }
                            if (!dt.Columns.Contains("Khối thi"))
                            {
                                error.Add("Cột Khối thi không tồn tại!");
                                goto next;
                            }
                            
                            for (int i = 0; i < dt.Rows.Count - 1; i++)
                            {

                                foreach (var a in ListGraduateSubject)
                                {
                                    CheckGraduateSubject = false;
                                    if (a.TenMon.ToLower().Trim() == dt.Rows[i]["Môn thi"].ToString().ToLower().Trim())
                                    {
                                        CheckGraduateSubject = true;
                                        IDGraduateSubject = a.MaMon.ToString().Trim();
                                        break;
                                    }
                                    //else
                                    //{
                                    //    error.Add("Khoa: " + dt.Rows[i]["Khoa"].ToString() + " không tồn tại!");
                                    //    CheckDepartment = false;
                                    //}
                                }
                                foreach (var b in ListGroup)
                                {
                                    CheckGroup = false;
                                    if (b.MaKhoiThi.ToLower().Trim() == dt.Rows[i]["Khối thi"].ToString().ToLower().Trim())
                                    {
                                        CheckGroup = true;
                                        break;
                                    }
                                    //else
                                    //{
                                    //    error.Add("Khối: " + dt.Rows[i]["khoithi"].ToString() + " không tồn tại!");
                                    //    CheckGroup = false;
                                    //}
                                }
                                foreach (var c in ListGraduateSubject_Group)
                                {
                                    CheckGraduateSubject_Group = false;
                                    if (c.MaKhoiThi.ToString().ToLower().Trim() == dt.Rows[i]["Khối thi"].ToString().ToLower().Trim() &&
                                        c.TbMonTotNghiep.TenMon.ToString().ToLower().Trim() == dt.Rows[i]["Môn thi"].ToString().ToLower().Trim())
                                    {
                                        CheckGraduateSubject_Group = true;
                                        break;
                                    }
                                }

                                //try
                                //{
                                if (CheckGraduateSubject && CheckGroup && !CheckGraduateSubject_Group)
                                {
                                    item = new TbKhoiThi_MonTotNghiep();
                                    item.MaMon = Int32.Parse(IDGraduateSubject);
                                    item.MaKhoiThi = dt.Rows[i]["Khối thi"].ToString().Trim();
                                    db.TbKhoiThi_MonTotNghieps.Add(item);
                                    db.SaveChanges();
                                }

                                //}
                                //catch
                                //{
                                //}
                            }
                            for (int k = 0; k < error.Count; k++)
                            {
                                count += " + " + error[k].ToString() + "<br/>";
                            }
                        next:
                            state = true;
                            mess = "Thực hiện chèn thành công " + (dt.Rows.Count - error.Count) + " bản ghi!";
                            //empData["Message"] = "Lưu thành công!";
                        }
                        else
                        {
                            Response.Write("Bạn phải nhập file định dạng excel!");
                        }
                        oledbcon.Close();
                        System.IO.File.Delete(path);
                    }

                }
            }
            else
                mess = "Có lỗi.";

            return Json(new { State = state, Message = mess });
        }
        #endregion
        private void SaveChanges(DbContext context)
        {
            try
            {
                context.SaveChanges();
            }
            catch (DbEntityValidationException ex)
            {
                StringBuilder sb = new StringBuilder();

                foreach (var failure in ex.EntityValidationErrors)
                {
                    sb.AppendFormat("{0} failed validation\n", failure.Entry.Entity.GetType());
                    foreach (var error in failure.ValidationErrors)
                    {
                        sb.AppendFormat("- {0} : {1}", error.PropertyName, error.ErrorMessage);
                        sb.AppendLine();
                    }
                }

                throw new DbEntityValidationException(
                    "Entity Validation Failed - errors follow:\n" +
                    sb.ToString(), ex
                ); // Add the original exception as the innerException
            }
        }
    }
}